package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;

public class Cart {
	private Set<CartItem> items = new HashSet<CartItem>();;
	private Map<Integer, Integer> quantities;
	private Cart() {

	}
	public void setQuantities(Map<Integer, Integer> quantities) {
		this.quantities = quantities;
	}

	

	public static Cart getCart(Set<Integer> inventoryIds)
			throws DatabaseException {
		if (inventoryIds == null || inventoryIds.isEmpty()) {
			return new Cart();
		}

		ResultSet rs = null;
		CartItem item = null;
		Cart cart = null;
		Color color = null;
		Figure figure = null;
		Size size = null;
		Present present = null;
		Producer producer = null;
		int inventoryId = -1;
		int previousInventoryId = -1;
		int presentId = 0;
		String query = prepareQuery(inventoryIds);
		// System.out.println(query);
		try (Connection con = ConnectionPool.getConnection();
				PreparedStatement statement = con.prepareStatement(query);) {
			rs = statement.executeQuery();

			while (rs.next()) {
				if (cart == null) {
					cart = new Cart();
					color = new Color(rs.getInt("color_id"),
							rs.getString("color_name"),
							rs.getString("color_path"),
							rs.getString("color_code"));
					figure = new Figure(rs.getString("figure_path"),
							rs.getInt("figure_color_id"));
					size = new Size(rs.getInt("size_id"),
							rs.getString("size_name"));
					producer = new Producer(rs.getInt("producer_id"),
							rs.getString("producer"));
					item = new CartItem(rs.getInt("item_id"),
							rs.getString("description"), rs.getDouble("price"),
							rs.getDouble("promotional_price"),
							rs.getString("brand_description"), producer,
							rs.getInt("inventory_id"), color, figure, size,
							rs.getInt("count"));
				}
				inventoryId = rs.getInt("inventory_id");
				if (inventoryId != previousInventoryId
						&& previousInventoryId != -1) {
					cart.addItem(item);
					color = new Color(rs.getInt("color_id"),
							rs.getString("color_name"),
							rs.getString("color_path"),
							rs.getString("color_code"));
					figure = new Figure(rs.getString("figure_path"),
							rs.getInt("figure_color_id"));
					size = new Size(rs.getInt("size_id"),
							rs.getString("size_name"));
					producer = new Producer(rs.getInt("producer_id"),
							rs.getString("producer"));
					item = new CartItem(rs.getInt("item_id"),
							rs.getString("description"), rs.getDouble("price"),
							rs.getDouble("promotional_price"),
							rs.getString("brand_description"), producer,
							rs.getInt("inventory_id"), color, figure, size,
							rs.getInt("count"));
				}
				presentId = rs.getInt("present_id");
				if (presentId != 0) {
					present = new Present(rs.getInt("present_id"),
							rs.getString("present_description"),
							rs.getString("present_figure_path"));
					item.addPresent(present);
				}
				previousInventoryId = inventoryId;
			}
			cart.addItem(item);
		} catch (SQLException e) {
			throw new DatabaseException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				throw new DatabaseException(e);
			}
		}
		return cart;

	}

	public static String getCartJson(Cart cart) {
		Gson gson = new GsonBuilder().serializeNulls().create();
		return gson.toJson(cart);
	}
	
	private static String prepareQuery(Set<Integer> inventoryIds) {
		String QUERY = "select inventory.inventory_id, inventory.item_id, inventory.count as count, "
				+ "sizes.id as size_id, sizes.name as size_name, "
				+ "colors.id as color_id, colors.name as color_name, colors.path as color_path, colors.code as color_code, "
				+ "figures.path as figure_path, figures.color_id as figure_color_id, "
				+ "presents.id as present_id, presents.description as present_description, presents.FIGURE_PATH as present_figure_path, "
				+ "items.description as description, items.price, items.promotional_price, "
				+ "brands.description as brand_description, "
				+ "producers.description as producer, producers.id as producer_id "
				+ "from clothing_inventory inventory "
				+ "join sizes on (inventory.size_id = sizes.id) "
				+ "join colors on (inventory.color_id = colors.id) "
				+ "join items on (inventory.item_id = items.id) "
				+ "join figures on (figures.item_id = items.id and figures.color_id = inventory.color_id) "
				+ "left outer join  item_presents on (items.id = item_presents.item_id) "
				+ "left outer join presents on (item_presents.present_id = presents.id) "
				+ "left outer join brands on ( items.brand_id = brands.id) "
				+ "left outer join producers on (items.producer_id = producers.id) "
				+ "where inventory.inventory_id in (";
		StringBuffer queryString = new StringBuffer(QUERY);

		boolean first = true;
		for (int id : inventoryIds) {
			if (first == true) {
				queryString.append(id);
				first = false;
			} else {
				queryString.append("," + id);
			}
		}
		queryString.append(") order by inventory.inventory_id");
		return queryString.toString();
	}

	private void addItem(CartItem item) {
		this.items.add(item);
	}
	// public static void main(String[] args) throws DatabaseException {
	// String[] inventoryIds = {"5","6","8"};
	// Cart cart = getCart(inventoryIds).get();
	// System.out.println(getCartJson(cart));
	// }

}
